﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
    public partial class MustSellListItemSSRepo
    {
        public bool Save(List<MustSellListItemSS> domains,List<MustSellListItemSSC> domainsSSC,List<MustSellListItemSupermarketSmallNew> domainsSmall)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {

                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE MustSellListItemSS";
                    cmd.CommandType = CommandType.Text;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    domains.ForEach(delegate(MustSellListItemSS domain)
                    {
                        //SqlCommand 
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "INSERT INTO MustSellListItemSS(KodeProduk, MSL, Region, Flag_aktif, ProductDescription) VALUES(@KodeProduk, @MSL, @Region, @Flagaktif, @ProductDescription)";
                        cmd.CommandType = CommandType.Text;

                        
                        //cmd.CommandText = "SP_MustSellListItemSS_Insert";
                        //cmd.CommandType = CommandType.StoredProcedure;
                        
                        cmd.Parameters.Add("@KodeProduk", SqlDbType.VarChar).Value = (object)domain.KodeProduk ?? DBNull.Value;
                        cmd.Parameters.Add("@MSL", SqlDbType.VarChar).Value = (object)domain.MSL ?? DBNull.Value;
                        cmd.Parameters.Add("@Region", SqlDbType.VarChar).Value = (object)domain.Region ?? DBNull.Value;
                        cmd.Parameters.Add("@Flagaktif", SqlDbType.VarChar).Value = (object)domain.Flagaktif ?? DBNull.Value;
                        cmd.Parameters.Add("@ProductDescription", SqlDbType.VarChar).Value = (object)domain.ProductDescription ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE MustSellListItemSSC";
                    cmd.CommandType = CommandType.Text;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    domainsSSC.ForEach(delegate(MustSellListItemSSC domain)
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "INSERT INTO dbo.MustSellListItemSSC(KodeProduk, MSL, Region, ProductDescription) VALUES(@KodeProduk, @MSL, @Region, @ProductDescription)";
                        cmd.CommandType = CommandType.Text;

                        //cmd.CommandText = "INSERT INTO dbo.MustSellListItemSSC(KodeProduk, MSL, Region, ProductDescription) VALUES(@KodeProduk, @MSL, @Region, @ProductDescription)";
                        //cmd.CommandText = "SP_MustSellListItemSSC_Insert";
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@KodeProduk", SqlDbType.NVarChar).Value = (object)domain.KodeProduk ?? DBNull.Value;
                        cmd.Parameters.Add("@MSL", SqlDbType.NVarChar).Value = (object)domain.MSL ?? DBNull.Value;
                        cmd.Parameters.Add("@Region", SqlDbType.NVarChar).Value = (object)domain.Region ?? DBNull.Value;
                        cmd.Parameters.Add("@ProductDescription", SqlDbType.VarChar).Value = (object)domain.ProductDescription ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE MustSellListItemSupermarketSmallNew";
                    cmd.CommandType = CommandType.Text;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    domainsSmall.ForEach(delegate(MustSellListItemSupermarketSmallNew domain)
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "INSERT INTO dbo.MustSellListItemSupermarketSmallNew(KodeProduk, MSL, Region, productdescription) VALUES(@KodeProduk, @MSL, @Region, @productdescription)";
                        cmd.CommandType = CommandType.Text;

                        //cmd.CommandText = "INSERT INTO dbo.MustSellListItemSupermarketSmallNew(KodeProduk, MSL, Region, productdescription) VALUES(@KodeProduk, @MSL, @Region, @productdescription)";
                        //cmd.CommandText = "SP_MustSellListItemSupermarketSmallNew_Insert";
                        //cmd.CommandType = CommandType.StoredProcedure;
                        
                        cmd.Parameters.Add("@KodeProduk", SqlDbType.NVarChar).Value = (object)domain.KodeProduk ?? DBNull.Value;
                        cmd.Parameters.Add("@MSL", SqlDbType.NVarChar).Value = (object)domain.MSL ?? DBNull.Value;
                        cmd.Parameters.Add("@Region", SqlDbType.NVarChar).Value = (object)domain.Region ?? DBNull.Value;
                        cmd.Parameters.Add("@productdescription", SqlDbType.VarChar).Value = (object)domain.productdescription ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });


                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }


            }
            return result;
        }
    }
}
